package com.baichang.taoshunda.dao.impl;

import com.baichang.framework.dao.impl.BaseDaoImpl;
import com.baichang.framework.model.Page;
import com.baichang.taoshunda.dao.BussinessDao;
import com.baichang.taoshunda.entity.Bussiness;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class BussinessDaoImpl extends BaseDaoImpl<Bussiness> implements BussinessDao {
    /**
     * 根据城市编号获取对应的推荐商家
     *
     * @param cityId 城市编号
     * @param lng
     * @param lat    @return
     */
    @Override
    public List<Map<String, Object>> getRecommendBussiness(Integer cityId, String lng, String lat) {
        String sql = "SELECT rc.company_id AS id,rc.picture AS bussLogo,tb.buss_name AS busName,rc.city_id AS cityId,rc.area_id AS areaId,rc.lng,rc.lat,ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN(((" + lat + ") * PI() / 180 - rc.lat * PI() / 180) / 2),2 " +
                "  ) + COS((" + lat + ") * PI() / 180) * COS(rc.lat * PI() / 180) * POW( " +
                "  SIN(((" + lng + ") * PI() / 180 - rc.lng * PI() / 180) / 2),2 " +
                "  ))) * 1000) AS juli FROM t_recommend_company rc INNER JOIN t_bussiness tb ON tb.id=rc.company_id " +
                " WHERE tb.city_id=? AND rc.status=1 AND rc.auditing_status=2 AND tb.state=1 AND DATE_FORMAT(rc.end_time,'%Y-%m-%d') >= DATE_FORMAT(NOW(),'%Y-%m-%d') ";
        sql += " ORDER BY juli LIMIT 20 ";
        return queryForMapList(sql, cityId);
    }

    /**
     * 查询商家列表
     *
     * @param startCreate
     * @param startTime
     * @param endTime
     * @param cityId
     * @param typeId
     * @param name
     * @param nowPage
     * @param pageSize    @return
     */
    @Override
    public Page queryBusinessList(String startCreate, String endCreate, String startTime, String endTime, Integer cityId, Integer typeId, String name, int nowPage, int pageSize) {
        List list = new ArrayList();
        String sql = "SELECT * FROM(SELECT tb.*,tc.name,bt.type_name AS typeName,tp.name AS promotionName,tl.link,tl.id AS cId FROM t_bussiness tb " +
                "INNER JOIN t_city tc ON tb.city_id=tc.id INNER JOIN t_buss_type bt ON bt.id=tb.buss_type_id " +
                "LEFT JOIN t_promotion tp ON tb.id=tp.buss_id LEFT JOIN t_carousel tl ON tl.link=tb.id " +
                "WHERE tb.state IN (1,2) AND tc.status IN (1) AND tb.is_recommend IN (0,1) AND tb.is_position IN (0,1) AND tb.audit IN (1) ";
        if (!startCreate.equals("") && !endCreate.equals("")) {
            sql += " AND tb.created>=? AND tb.created <=DATE_ADD(?, Interval 1 DAY)";
            list.add(startCreate);
            list.add(endCreate);
        }
        if (!startTime.equals("") && !endTime.equals("")) {
            sql += " AND tb.last_time>=? AND tb.last_time<=DATE_ADD(?, Interval 1 DAY)";
            list.add(startTime);
            list.add(endTime);
        }
        if (cityId != 0) {
            sql += " AND tb.city_id=?";
            list.add(cityId);
        }
        if (typeId != 0) {
            sql += " AND tb.buss_type_id=?";
            list.add(typeId);
        }
        if (StringUtils.isNotBlank(name)) {
            sql += " AND tb.buss_name LIKE ?";
            list.add("%" + name + "%");
        }
        sql += " GROUP BY id ORDER BY created DESC ) A";
        return pageForMapList(sql, nowPage, pageSize, list.toArray());
    }

    /**
     * 查询商家订单
     *
     * @param
     * @param
     * @param id
     * @param nowPage
     * @param pageSize @return
     */
    @Override
    public Page<Map<String, Object>> businessOrderList(Integer id, int nowPage, int pageSize) {
        String sql = "SELECT tr.order_number AS orderNumber,tu.nick_name AS nickName,tr.order_state AS orderState,tr.pay_type AS payType,tr.created,tr.goods_adress AS goodsAddress \n" +
                "FROM t_order tr INNER JOIN t_bussiness tb ON tb.id=tr.seller_id INNER JOIN t_user tu ON tu.id=tr.buyer_id \n" +
                "WHERE seller_id = ? ORDER BY tr.created DESC";
        return pageForMapList(sql, nowPage, pageSize, id);
    }

    /**
     * 商家明细查询
     *
     * @param id
     * @param nowPage
     * @param pageSize @return
     */
    @Override
    public Page<Map<String, Object>> businessOrderDetails(Integer id, int nowPage, int pageSize) {
        String sql = "SELECT cb.source_type AS sourceType,cb.balance,cb.created FROM t_company_balance cb INNER JOIN t_bussiness tb ON cb.buss_id = tb.id WHERE cb.buss_id=? ORDER BY cb.created DESC ";
        return pageForMapList(sql, nowPage, pageSize, id);
    }

    /**
     * 商家禁用、启用
     *
     * @param id
     * @param state
     * @return
     */
    @Override
    public int updateStatus(Integer id, Integer state) {
        String sql = "UPDATE {table} SET state=? WHERE id=?";
        return update(sql, state, id);
    }

    /**
     * 修改商家信息
     *
     * @param map
     * @param id
     * @return
     */
    @Override
    public int update(Map map, Integer id) {
        String sql = "id=?";
        return update(sql, map, id);
    }

    /**
     * 获取诚信商家的数量
     *
     * @param
     * @return
     */
    @Override
    public Map<String, Object> getIsRecommend() {
        String sql = "SELECT COUNT(is_recommend) AS recommend FROM t_bussiness WHERE is_recommend IN (1) ";
        return queryForMap(sql);
    }

    /**
     * 查询审核商家
     *
     * @param businessName
     * @param account
     * @param telephone
     * @param cityName
     * @param address
     * @param nowPage
     * @param pageSize     @return
     */
    @Override
    public Page queryBusinessAudit(String businessName, String account, String telephone, String cityName, String address, int nowPage, int pageSize) {
        List<Object> obj = new ArrayList<>();
        String sql = "SELECT tb.*,tc.name AS cityName FROM t_bussiness tb LEFT JOIN t_city tc ON tb.city_id=tc.id " +
                "WHERE tb.audit IN (3) AND tc.status=1 AND tb.state=1 and register_number=3";
        if (StringUtils.isNotBlank(businessName)) {
            sql += " AND tb.buss_name LIKE ?";
            obj.add("%" + businessName + "%");
        }
        if (StringUtils.isNotBlank(account)) {
            sql += " AND tb.account LIKE ?";
            obj.add("%" + account + "%");
        }
        if (StringUtils.isNotBlank(telephone)) {
            sql += " AND tb.buss_tel LIKE ?";
            obj.add("%" + telephone + "%");
        }
        if (StringUtils.isNotBlank(cityName)) {
            sql += " AND tc.name LIKE ?";
            obj.add("%" + cityName + "%");
        }
        if (StringUtils.isNotBlank(address)) {
            sql += " AND tb.adress LIKE ?";
            obj.add("%" + address + "%");
        }
        return pageForMapList(sql, nowPage, pageSize, obj.toArray());
    }

    /**
     * 修改诚信商家
     *
     * @param id
     * @param isRecommend
     * @return
     */
    @Override
    public int updateRecommend(Integer id, Integer isRecommend) {
        String sql = "UPDATE t_recommend_company SET status=? WHERE id=?";
        return update(sql, isRecommend, id);
    }

//    /**
//     * 审核商家
//     *
//     * @param id
//     * @param audit
//     * @return
//     */
//    @Override
//    public int updateAudit(Integer id, Integer audit, String reason) {
//        if (reason.equals("") && reason == null) {
//            String sql = "UPDATE t_bussiness SET audit=? WHERE id=?";
//            return update(sql, audit, id);
//        } else {
//            String sql = "UPDATE t_bussiness SET audit=?,reason=? WHERE id=?";
//            return update(sql, audit, reason, id);
//        }
//    }

    /**
     * 修改商家账号
     *
     * @param id
     * @param passWord
     * @return
     */
    @Override
    public int updateBusinessAccount(Integer id, String passWord) {
        String sql = "update {table} set pwd=? where id=?";
        return update(sql, passWord, id);
    }

    /**
     * 查询商家详情
     * 接口
     * 武圣路
     *
     * @param bussId 商家编号
     * @return
     */
    @Override
    public Map<String, Object> getBussinessDateial(int bussId) {
        String sql = "SELECT b.id, b.head_pic,b.buss_name,b.is_open,b.is_invoice,CONCAT(b.shop_pic,\",\",b.store_pic) AS bannerImages, b.background_img,b.is_dispatching,b.head_pic as bussLogo, b.state, b.dispatching, b.lng,b.hx_name,b.hx_pwd, b.lat, b.start_time, b.buss_tel, b.adress, b.end_time, b.balance FROM t_bussiness b WHERE  b.id=?";
        //禁用商家也可查询   b.state=1 AND
        return queryForMap(sql, bussId);
    }

    /**
     * 查询商家列表
     *
     * @param typeId     查询类型 1综合排序2 销量 3距离
     * @param cityId     城市编号
     * @param nowPage    当前页
     * @param pageSize   @return
     * @param bussTypeId
     * @param ulat
     * @param ulng
     */
    @Override
    public List<Map<String, Object>> getBussinessList(int typeId, int cityId, int nowPage, int pageSize, int bussTypeId, int userId, double ulat, double ulng) {
        List list = new ArrayList();
        //AND DATE_FORMAT(created, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m'))
        String sql = "SELECT ( SELECT IFNULL(SUM(( SELECT SUM(od.count) FROM t_order_details od WHERE od.order_number = o.order_number )), 0) AS count FROM t_order o WHERE (o.seller_id = b.id AND order_state IN (5, 6, 9, 13, 14, 15, 16) ) ) AS monthCount , ( SELECT COUNT(id) AS count FROM t_goods g WHERE (g.company_id = b.id AND `status` = 1 AND is_upper = 1) ) AS goodsCount, b.id, b.buss_name, b.is_invoice, b.is_dispatching , b.start_time, b.end_time, b.is_open, b.store_pic AS bussLogo, b.state , b.dispatching , ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN(( " + ulat + "  * PI() / 180 - lat * PI() / 180) / 2), 2) + COS( " + ulat + " * PI() / 180) * COS(lat * PI() / 180) * POW(SIN(( " + ulng + "  * PI() / 180 - lng * PI() / 180) / 2), 2))) * 1000) AS juli , b.lng, b.lat , ( SELECT IFNULL(SUM(number), 0) FROM t_shop_car sc WHERE sc.buss_id = b.id AND sc.user_id = ? ) AS shopNumber , ( SELECT COUNT(0) FROM `t_business_money_record` WHERE (`buiness_ud` = b.id AND `status` = '1' AND type = 1) ) AS isRecord FROM t_bussiness b WHERE  state = 1 AND b.audit = 1  AND b.buss_type_id = ?  ";
        list.add(userId);
        list.add(bussTypeId);
        if (bussTypeId != 57) {
            sql += " AND city_id = ? ";
            list.add(cityId);

        }
        if (typeId == 1) {
            sql += " ORDER BY monthCount desc,juli asc ";
        } else if (typeId == 2) {
            sql += " ORDER BY monthCount desc,b.id desc";
        } else {
            sql += " ORDER BY juli asc ";
        }
        System.out.println(sql);
        return listForMapList(sql, nowPage, pageSize, list.toArray());
    }


    /**
     * 根据用户id查询邀请商家商家列表
     *
     * @param userId   用户id
     * @param nowPage  当前页
     * @param pageSize @return
     * @param ulat
     * @param ulng
     */
    @Override
    public List<Map<String, Object>> getBussinessListByUserId(int userId, int nowPage, int pageSize, double ulat, double ulng) {
        List list = new ArrayList();
        // AND DATE_FORMAT(created, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
        String sql = "SELECT ( SELECT IFNULL(SUM(( SELECT SUM(od.count) FROM t_order_details od WHERE od.order_number = o.order_number )), 0) AS count FROM t_order o WHERE (o.seller_id = b.id AND order_state IN (5, 6, 9, 13, 14, 15, 16) )) AS monthCount , ( SELECT COUNT(id) AS count FROM t_goods g WHERE (g.company_id = b.id AND `status` = 1 AND is_upper = 1) ) AS goodsCount, b.id, b.buss_name, b.is_invoice, b.is_dispatching , b.start_time, b.end_time, b.is_open, b.store_pic AS bussLogo, b.state , b.dispatching , b.id , ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN(( " + ulat + "  * PI() / 180 - lat * PI() / 180) / 2), 2) + COS( " + ulat + " * PI() / 180) * COS(lat * PI() / 180) * POW(SIN(( " + ulng + "  * PI() / 180 - lng * PI() / 180) / 2), 2))) * 1000) AS juli , b.lng, b.lat , 0 AS shopNumber , ( SELECT COUNT(0) FROM `t_business_money_record` WHERE (`buiness_ud` = b.id AND `status` = '1' AND type = 1) ) AS isRecord FROM t_bussiness b WHERE  state = 1 AND b.audit = 1  and b.user_id=?  ";
        list.add(userId);
        sql += " ORDER BY juli asc ";
        System.out.println(sql);
        return listForMapList(sql, nowPage, pageSize, list.toArray());
    }

    /**
     * 根据手机号获取商家信息
     *
     * @param phone 手机号
     * @return
     */
    @Override
    public Bussiness getBussnessByPhone(String phone) {
        //AND state=1 商家禁用可以登录   AND audit!=2
        String sql = " SELECT * FROM {table} WHERE account=?  ORDER BY FIELD(`state`,1,2,0) ,created DESC";
        return queryForBean(sql, phone);
    }

    /**
     * 根据手机号获取商家信息
     *
     * @param phone
     * @return
     */
    @Override
    public Bussiness getAuditBussnessByPhone(String phone) {
        //AND state=1
        String sql = " SELECT * FROM {table} WHERE account=?  AND audit!=2 ORDER BY FIELD(`state`,1,2,0) ,created DESC ";
        return queryForBean(sql, phone);
    }

    /**
     * 忘记密码修改密码
     *
     * @param account 账号
     * @param newPwd  新密码
     * @return
     */
    @Override
    public int updateForgetPwd(String account, String newPwd) {
        String sql = " UPDATE {table} SET pwd=? WHERE account=? AND audit!=2 ";
        return update(sql, newPwd, account);
    }

    /**
     * 修改商家个人信息
     *
     * @param bussId  商家编号
     * @param headPic 头像
     * @param name    名称
     * @param sex
     * @return
     */
    @Override
    public int updateBusinessInfo(Integer bussId, String headPic, String name, String sex) {
        String sql = " UPDATE {table} SET head_pic=?,buss_name=?,sex=? WHERE id=? ";
        return update(sql, headPic, name, sex, bussId);
    }

    /**
     * 修改账号
     *
     * @param bussId     商家编号
     * @param newAccount 账号
     * @return
     */
    @Override
    public int updateBussinessAccount(Integer bussId, String newAccount) {
        String sql = " UPDATE {table} SET account=? WHERE id=? ";
        return update(sql, newAccount, bussId);
    }

    /**
     * 修改密码
     *
     * @param bussId 商家编号
     * @param pwd    密码
     * @return
     */
    @Override
    public int updatePwd(Integer bussId, String pwd) {
        String sql = " UPDATE {table} SET pwd=? WHERE id=? ";
        return update(sql, pwd, bussId);
    }

    /**
     * 推荐商家广告位
     *
     * @param id
     * @param isPosition
     * @return
     */
    @Override
    public int updateIsPosition(Integer id, Integer isPosition) {
        String sql = "UPDATE {table} SET is_position=? WHERE id=? ";
        return update(sql, isPosition, id);
    }

    /**
     * 查询推荐商家
     *
     * @param name
     * @param cityName
     * @param nowPage
     * @param pageSize @return
     */
    @Override
    public Page queryRecommendBusiness(String name, String cityName, int nowPage, int pageSize) {
        List list = new ArrayList();
        String sql = "SELECT rc.*,tb.buss_name AS busName,tc.name,tb.head_pic AS headPic FROM t_recommend_company rc " +
                "INNER JOIN t_bussiness tb ON rc.company_id=tb.id INNER JOIN t_city tc ON tc.id=rc.city_id" +
                " WHERE rc.auditing_status=2 AND rc.status=1 AND DATE_FORMAT(rc.end_time,'%Y-%m-%d')>= DATE_FORMAT(NOW(),'%Y-%m-%d')";
        if (StringUtils.isNotBlank(name)) {
            sql += " AND tb.buss_name LIKE ?";
            list.add("%" + name + "%");
        }
        if (StringUtils.isNotBlank(cityName)) {
            sql += " AND tc.name LIKE ?";
            list.add("%" + cityName + "%");
        }
        sql += " ORDER BY tb.sorting DESC ";
        return pageForMapList(sql, nowPage, pageSize, list.toArray());
    }

    /**
     * 推荐商家广告位
     *
     * @param name
     * @param cityId
     * @param nowPage
     * @param pageSize @return
     */
    @Override
    public Page queryRecommendAdv(String name, Integer cityId, int nowPage, int pageSize) {
        List list = new ArrayList();
        String sql = "SELECT ty.`name` AS areaName, tb.buss_name AS busName, tc.id, tc.`status`, tc.created , tc.pic_path AS picPath, tc.link, tc.link_type AS linkType, tc.order_num AS orderNum, tc.city_id , tc.refuse_reason, tc.pass, tc.start_time, tc.end_time, tc.lng , tc.lat, tc.area_id FROM t_carousel tc INNER JOIN t_city ty ON ty.id = tc.area_id INNER JOIN t_bussiness tb ON tb.id = tc.link WHERE tc.STATUS = 1 AND tc.pass = 2";
        if (StringUtils.isNotBlank(name)) {
            sql += " AND tb.buss_name LIKE ?";
            list.add("%" + name + "%");
        }
        if (cityId > 0) {
            sql += " AND tc.city_id LIKE ?";
            list.add(cityId);
        }
        sql += " ORDER BY tc.order_num DESC ";
        return pageForMapList(sql, nowPage, pageSize, list.toArray());
    }

    /**
     * 修改是否营业
     *
     * @param bussId 商家编号
     * @param isOpen 是否营业
     * @return
     */
    @Override
    public int updateShopIsOpen(Integer bussId, int isOpen) {
        String sql = " UPDATE {table} SET is_open=? WHERE id=? ";
        return update(sql, isOpen, bussId);
    }

    @Override
    public List<Map<String, Object>> getBusinessByKey(int cityId, String key, int bussTypeId, int nowPage, int pageSize, String address) {
        // AND DATE_FORMAT(created, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
        String sql = "SELECT (SELECT IFNULL(SUM(( SELECT SUM(od.count) FROM t_order_details od WHERE od.order_number = o.order_number )), 0) AS count FROM t_order o WHERE o.seller_id =  b.id AND order_state IN (5, 6, 9, 13, 14, 15, 16) )) AS monthCount, ( SELECT COUNT(id) AS count FROM t_goods g WHERE g.company_id = b.id AND `status` = 1 ) AS goodsCount, ( SELECT IFNULL(AVG(star_num), 0) AS number FROM t_goods_comment gc WHERE gc.bus_id = b.id ) AS starNumber, b.id, b.buss_name , b.start_time, b.end_time, b.is_open,  b.store_pic as bussLogo, b.state , b.dispatching, b.lng, b.lat, ( SELECT IFNULL(SUM(number), 0) FROM t_shop_car sc WHERE sc.buss_id = b.id AND sc.user_id = 18 ) AS shopNumber , ( SELECT COUNT(0) FROM `t_business_money_record` WHERE `buiness_ud` = b.id AND `status` = '1' AND type = 1 ) AS isRecord FROM t_bussiness b WHERE city_id =?  AND b.audit=1 AND state = 1 ";
        if (!key.isEmpty() || !key.equals("")) {
            sql += "  AND buss_name LIKE '%" + key + "%'";
        }
        if (!address.isEmpty() || !address.equals("")) {
            sql += "AND b.adress LIKE '%" + address + "%' ";

        }
        if (bussTypeId != 0) {
            sql += " AND b.buss_type_id = ?";
            return listForMapList(sql, nowPage, pageSize, cityId, bussTypeId);
        }
        return listForMapList(sql, nowPage, pageSize, cityId);

    }

    /**
     * 查询未通过的商家
     *
     * @param businessName
     * @param account
     * @param telephone
     * @param cityName
     * @param address
     * @param nowPage
     * @param pageSize
     * @return
     */
    @Override
    public Page queryNotBusiness(String businessName, String account, String telephone, String cityName, String address, int nowPage, int pageSize) {
        List<Object> obj = new ArrayList<>();
        String sql = "SELECT tb.*,tc.name AS cityName FROM t_bussiness tb INNER JOIN t_city tc ON tb.city_id=tc.id " +
                "WHERE tb.audit IN (2) AND tc.status=1 AND tb.state=1";
        if (StringUtils.isNotBlank(businessName)) {
            sql += " AND tb.buss_name LIKE ?";
            obj.add("%" + businessName + "%");
        }
        if (StringUtils.isNotBlank(account)) {
            sql += " AND tb.account LIKE ?";
            obj.add("%" + account + "%");
        }
        if (StringUtils.isNotBlank(telephone)) {
            sql += " AND tb.buss_tel LIKE ?";
            obj.add("%" + telephone + "%");
        }
        if (StringUtils.isNotBlank(cityName)) {
            sql += " AND tc.name LIKE ?";
            obj.add("%" + cityName + "%");
        }
        if (StringUtils.isNotBlank(address)) {
            sql += " AND tb.adress LIKE ?";
            obj.add("%" + address + "%");
        }
        return pageForMapList(sql, nowPage, pageSize, obj.toArray());
    }

    /**
     * 修改商家的背景图片
     *
     * @param bussId        商家编号
     * @param backgroundPic 背景图片
     * @return
     */
    @Override
    public int updateBussinessBackgroundPic(Integer bussId, String backgroundPic) {
        String sql = " UPDATE {table} SET background_img=? WHERE id=? ";
        return update(sql, backgroundPic, bussId);
    }

    /**
     * 查询商家优惠活动
     *
     * @param businessName
     * @param nowPage
     * @param pageSize
     * @return
     */
    @Override
    public Page queryBusinessActivity(String businessName, int nowPage, int pageSize) {
        String sql = "SELECT tp.*,tb.account,tb.buss_name AS Bname FROM t_promotion tp LEFT JOIN t_bussiness tb ON tp.buss_id=tb.id";
        if (StringUtils.isNotBlank(businessName)) {
            sql += " WHERE tb.buss_name LIKE ?";
            return pageForMapList(sql, nowPage, pageSize, "%" + businessName + "%");
        }
        return pageForMapList(sql, nowPage, pageSize);
    }

    /**
     * 根据城市查询商家
     *
     * @param id
     * @return
     */
    @Override
    public List<Map<String, Object>> queryBusinessCity(Integer id) {
        String sql = "SELECT tb.id,tb.buss_name AS name FROM t_bussiness tb WHERE city_id=? AND state=1";
        return queryForMapList(sql, id);
    }

    /**
     * 修改商家余额
     *
     * @param bussId     商家编号
     * @param afterMoney 余额
     * @return
     */
    @Override
    public int updateBussinessBalance(Integer bussId, Double afterMoney) {
        String sql = " UPDATE {table} SET balance=? WHERE id=? ";
        return update(sql, afterMoney, bussId);
    }

    /**
     * 修改推荐状态
     *
     * @param link
     * @return
     */
    @Override
    public int updateBusState(Integer link) {
        String sql = " UPDATE {table} SET is_position=0 WHERE id=? ";
        return update(sql, link);
    }

    /**
     * 诚信商家修改排序
     *
     * @param id
     * @param sorting
     * @return
     */
    @Override
    public int updateSorting(Integer id, Integer sorting) {
        String sql = "UPDATE {table} SET sorting=? WHERE id=?";
        return update(sql, sorting, id);
    }

    /**
     * 修改商家详情信息
     *
     * @param id               商家编号
     * @param startTime        开始时间
     * @param endTime          结束时间
     * @param phone            电话
     * @param area             地区
     * @param cityId           城市编号
     * @param address          地址
     * @param lat              纬度
     * @param lng              经度
     * @param isInvoice
     * @param shopPic
     * @param storePic         @return
     * @param provinceId
     * @param areaId
     * @param minCityId
     * @param otherLicenses
     * @param otherLicensesTwo
     * @param bussLicnese
     * @param serviceLince
     */
    @Override
    public int updateBussDetails(Integer id, String startTime, String endTime, String phone, String area, Integer cityId, String address, String lat, String lng, Integer isDispatching, String isInvoice, String shopPic, String storePic, Integer provinceId, Integer areaId, int minCityId, String otherLicenses, String otherLicensesTwo, String bussLicnese, String serviceLince) {
        String sql = " UPDATE {table} SET start_time=?,end_time=?,buss_tel=?,area=?,city_id=?,adress=?,lat=?,lng=?,is_dispatching=?,is_invoice=?,store_pic=?,shop_pic=?,min_city_id=?,area_id=?,provinces_id=?,buss_licnese =?, service_lince =?, other_licenses = ?, other_licenses_two =? WHERE id=? ";
        return update(sql, startTime, endTime, phone, area, cityId, address, lat, lng, isDispatching, isInvoice, storePic, shopPic, minCityId, areaId, provinceId, bussLicnese, serviceLince, otherLicenses, otherLicensesTwo, id);
    }

    @Override
    public List<Map<String, Object>> getBusinessByAddress(int cityId, String address, int bussTypeId, int nowPage, int pageSize) {
        //AND DATE_FORMAT(created, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
        String sql = "SELECT (SELECT IFNULL(SUM(( SELECT SUM(od.count) FROM t_order_details od WHERE od.order_number = o.order_number )), 0) AS count FROM t_order o WHERE o.seller_id =  b.id AND order_state IN (5, 6, 9, 13, 14, 15, 16)) ) AS monthCount, ( SELECT COUNT(id) AS count FROM t_goods g WHERE g.bus_id = b.id AND `status` = 1 ) AS goodsCount, ( SELECT IFNULL(AVG(star_num), 0) AS number FROM t_goods_comment gc WHERE gc.bus_id = b.id ) AS starNumber, b.is_dispatching, b.id, b.buss_name , b.start_time, b.end_time, b.is_open, b.head_pic as bussLogo, b.state , b.dispatching, b.lng, b.lat, ( SELECT IFNULL(SUM(number), 0) FROM t_shop_car sc WHERE sc.buss_id = b.id AND sc.user_id = 18 ) AS shopNumber , ( SELECT COUNT(0) FROM `t_business_money_record` WHERE `buiness_ud` = b.id AND `status` = '1' AND type = 1 ) AS isRecord FROM t_bussiness b WHERE b.city_id = ? AND b.adress LIKE '%" + address + "%'  AND state = 1 AND b.audit=1";
        if (bussTypeId != 0) {
            sql += " AND b.buss_type_id = ?";
            return listForMapList(sql, nowPage, pageSize, cityId, bussTypeId);
        }
        return listForMapList(sql, nowPage, pageSize, cityId);
    }

    @Override
    public boolean updateHxbussInfo(Long bussId, String s, Object emPwd) {
        String sql = "UPDATE `t_bussiness` SET `hx_name`=?, `hx_pwd`=? WHERE id=?";
        return update(sql, s, emPwd, bussId) > 0;
    }

    /**
     * 查询该商家下的诚信和广告位
     *
     * @param id
     * @return
     */
    @Override
    public Map<String, Object> queryBusInfo(Integer id) {
        String sql = " SELECT * FROM {table} WHERE id=? ";
        return queryForMap(sql, id);
    }

    /**
     * 更新最后登录时间
     *
     * @param account
     * @return
     */
    @Override
    public int updateLastLoginTime(String account) {
        String sql = "UPDATE t_bussiness SET last_time=NOW() WHERE account=?";
        return update(sql, account);
    }

    @Override
    public int getTotal(int cityId, String address, int bussTypeId) {
        String sql = "SELECT count(0) FROM t_bussiness b WHERE b.city_id = ? AND b.adress LIKE '%" + address + "%' ";
        if (bussTypeId != 0) {
            sql += " AND b.buss_type_id = ?";
            return queryForInt(sql, cityId, bussTypeId);
        }
        return queryForInt(sql, cityId);
    }

    @Override
    public int getTotalByKey(int cityId, String key, int bussTypeId) {
        String sql = "SELECT count(0) FROM t_bussiness b WHERE city_id =?   AND state = 1 ";
        if (!key.isEmpty() || key.equals("")) {
            sql += "  AND buss_name LIKE '%" + key + "%'";
        }
        if (bussTypeId != 0) {
            sql += " AND b.buss_type_id = ?";
            return queryForInt(sql, cityId, bussTypeId);
        }
        return queryForInt(sql, cityId);

    }

    @Override
    public int getTotal(int typeId, int cityId, int bussTypeId, int userId) {
        String sql = "SELECT count(0) from t_bussiness b WHERE city_id = ? AND state = 1 AND b.buss_type_id = ?  ";

        return queryForInt(sql, cityId, bussTypeId);
    }

    /**
     * 修改排名
     *
     * @param id
     * @return
     */
    @Override
    public int updateSort(Integer id) {
        String sql = "UPDATE t_bussiness SET sorting=0 WHERE id=?";
        return update(sql, id);
    }

    @Override
    public void updateRYToken(Long bussId, String ryToken, String trendsUserId) {
        String sql = "update {table} SET ry_token=?,trends_user_id=? WHERE id=?";
        update(sql, ryToken, trendsUserId, bussId);
    }

    /**
     * 添加设置提现密码
     *
     * @param bussId
     * @param cashPwd
     * @return
     */
    @Override
    public boolean addCashPwd(Integer bussId, String cashPwd) {
        String sql = "UPDATE `t_bussiness` SET `cash_pwd`=? WHERE (`id`=?)";
        return update(sql, cashPwd, bussId) > 0;
    }

    /**
     * @return
     */
    @Override
    public Map<String, Object> businessCount() {
        String sql = "SELECT COUNT(*) AS businessCount FROM t_bussiness WHERE state=1 AND audit=1";
        return queryForMap(sql);
    }

    /**
     * @param busId
     * @param money
     * @return
     */
    @Override
    public int updateBalance(Integer busId, BigDecimal money) {
        String sql = "UPDATE `t_bussiness` SET `balance`=? WHERE `id`=?";
        return update(sql, money, busId);
    }

    @Override
    public Bussiness getCompanyById(Integer busId) {
        String sql = "SELECT * FROM t_bussiness WHERE id=? AND state=1";
        return queryForBean(sql, busId);
    }

    /**
     * 添加商家冻结金额
     *
     * @param sellerId
     * @param money
     */
    @Override
    public void updateFreezeMoney(Integer sellerId, double money) {
        String sql = "UPDATE `t_bussiness` SET `freeze_money`=? WHERE (`id`=? )";
        update(sql, money, sellerId);
    }

    @Override
    public int getCompanyCountByTypeId(Integer id) {
        String sql = "SELECT COUNT(id) FROM t_bussiness   WHERE buss_type_id=? AND state=1";
        return queryForInt(sql, id);
    }

    /**
     * @param companyTypeId 装备商城类型编号
     * @return
     */
    @Override
    public Bussiness getBussinessByTypeId(int companyTypeId) {
        String sql = "SELECT * FROM t_bussiness WHERE buss_type_id=? LIMIT 1";
        return queryForBean(sql, companyTypeId);
    }

    @Override
    public Integer getRebateCount(Integer userId) {
        String sql = "SELECT COUNT(0) FROM t_bussiness WHERE user_id=?";
        return queryForInt(sql, userId);
    }

    @Override
    public boolean registerSecondStep(Integer companyId, String area, Integer provinceId, Integer registerNumber, String servicePhone, String name, String address, Integer cityId, Integer areaId, Integer typeId, String lat, String lng, String legalPeople, String emergencyContact, String emergencyContactTel, int minCityId) {
        String sql = "UPDATE `t_bussiness` SET adress=?,area=?,provinces_id=?,area_id=?,city_id=?,min_city_id=?," +
                "buss_name=?,service_lince=?,register_number=?, buss_type_id=? ,lat=?,lng=?,emergency_contact=?," +
                "emergency_contact_tel=?,legal_people=?,audit=3 WHERE id=? ";
        return update(sql, address, area, provinceId, areaId, cityId, minCityId, name, servicePhone, registerNumber, typeId, lat, lng, emergencyContact, emergencyContactTel, legalPeople, companyId) > 0;
    }

    @Override
    public boolean registerThreeStep(Integer companyId, Integer registerNumber, String storePic, String shopPic, String legalCardPic, String handheldPhotos, String fontCard, String reverseCard, String bussLicnese, String serviceLince) {
        String sql = "UPDATE `t_bussiness` SET `buss_licnese`=?,register_number=?, `store_pic`=?, `shop_pic`=?, `service_lince`=?, `legal_card_pic`=?,font_card=?,handheld_photos=?,reverse_crad=?,audit=3 WHERE id=?";
        return update(sql, bussLicnese, registerNumber, storePic, shopPic, serviceLince, legalCardPic, fontCard, handheldPhotos, reverseCard, companyId) > 0;
    }
}